﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Diagnostics;
using System.Data;
using Amib.Threading;
using Shutterstock.Salesforce.SSISDownload.Logging;
using Shutterstock.Salesforce.SSISDownload.SFTableOperations;
using System.IO;
using System.Net.Mail;
using Shutterstock.Salesforce.Tools;

namespace Shutterstock.Salesforce.SSISDownload
{
    public static class SFTask
    {
        static ReportingLevel _reportLevel = ReportingLevel.Low;
        
        static int _errorCount;

        static object objectLock = new object();
        static IDTSComponentEvents _componentEvents;

        static SmartThreadPool _threadPool;
        static IWorkItemsGroup _taskWorkGroup;

        static StringBuilder _Message = new StringBuilder();
        static StringBuilder _ErrorMessage = new StringBuilder();
        
        static List<ReplicationParameter> _FailedReplicateList = new List<ReplicationParameter>();
        static List<ReplicationParameter> _FailedRefreshList = new List<ReplicationParameter>();

        /// <summary>
        /// Do the task
        /// </summary>
        /// <param name="taskType">Type of the task</param>
        /// <param name="maxThreads">The max number of threads</param>
        /// <param name="sqlDestConnectionString">Sql destination connection string</param>
        /// <param name="sfUserName">Salesforce service username</param>
        /// <param name="sfPassword">Salesforce service password</param>
        /// <param name="batchSize">Batch size for bulk insert</param>
        /// <param name="MaintainIndexes">Whether maintain the indexes</param>
        /// <param name="MaintainPermissions">Whether maintain the permissions</param>
        /// <param name="manualComparison">Whether do the manual comparison</param>
        /// <param name="messageLevel">Message level</param>
        /// <param name="componentEvents">ComponentEvents</param>
        /// <param name="Retries"> Number of Retries per task</param>
        /// <returns></returns>
        public static bool DoTask(ReplicationTaskType taskType, int maxThreads, string sqlDestConnectionString, string sfUserName, string sfPassword,
            int batchSize, bool MaintainIndexes, bool MaintainPermissions, bool manualComparison, bool useBulkAPI, int bulkCutoff, ReportingLevel reportLevel, IDTSComponentEvents componentEvents, int Retries, ExceptionParameter exceptParam, DmlEmailParameter emailParameter)
        {
            DateTime start = DateTime.Now;
            _componentEvents = componentEvents;
            _reportLevel = reportLevel;
            List<ReplicationParameter> replicateList = new List<ReplicationParameter>();
            List<ReplicationParameter> refreshList = new List<ReplicationParameter>();

            Dictionary<string, bool> exceptionList = GetExceptionList(sqlDestConnectionString, exceptParam);
            Dictionary<string, bool> forceBulkList = GetForceBulkList(sqlDestConnectionString, exceptParam);
            Dictionary<string, bool> forceStandardList = GetForceStandardList(sqlDestConnectionString, exceptParam);

            _errorCount = 0;

            DisplayInformation(String.Format("Task Type: {0}", taskType));
            DisplayInformation(String.Format("SF UserName: {0}", sfUserName));
            DisplayInformation(String.Format("Manual Comparison: {0}", (manualComparison ? "Yes" : "No")));
            DisplayInformation(String.Format("Use Bulk API?: {0}", (useBulkAPI ? "Yes" : "No")));
            DisplayInformation(String.Format("Bulk Cutoff: {0}", bulkCutoff));
            DisplayInformation(String.Format("Allow Exceptions: {0}", (exceptParam.AllowExceptions ? "Yes" : "No")));
            DisplayInformation(String.Format("Default Status: {0}", (exceptParam.DefaultStatus ? "Include" : "Exclude")));
            
            
            Logger.Init(sqlDestConnectionString);


            ISalesForceConnection sfSource = SalesForceConnectionFactory.Build(sfUserName,sfPassword); // TODO: IOC

            
            // get a list of queryable table name;
            List<string> tables = sfSource.SalesForceService.GetQueryableTables();
            SFTableInfo tableInfo;
            string bulkPath = Path.GetTempPath();
            DisplayInformation(String.Format("Starting To Build Parameters"));

            
            foreach (string tableName in tables)
            {
                tableInfo = sfSource.SalesForceService.GetTableInfo(tableName);
                if (tableInfo.Queryable)
                {
                    bool useBulk = useBulkAPI;
                    int cutoff = bulkCutoff;
                    if (forceBulkList.ContainsKey(tableName))
                    {
                        useBulk = true;
                        cutoff = int.MinValue;
                    }
                    else if (forceStandardList.ContainsKey(tableName)) 
                    {
                        useBulk = false;
                    }

                    ReplicationParameter para = new ReplicationParameter(sqlDestConnectionString, sfSource,
                        tableInfo, batchSize, MaintainIndexes, MaintainPermissions, manualComparison,useBulk,cutoff,bulkPath,emailParameter);

                    if (exceptParam.AllowExceptions)
                    {
                        if (exceptParam.DefaultStatus) //if Include, then exceptions are exclusions
                        {
                            if (exceptionList.ContainsKey(tableName))
                            {
                                DisplayInformation(String.Format("Skipping Table Name {0}", tableName));
                                continue;
                            }
                        }
                        else
                        {
                            if (!(exceptionList.ContainsKey(tableName.ToLower())))
                            {
                                DisplayInformation(String.Format("Skipping Table Name {0}", tableName));
                                continue;
                            }
                        }
                    }
                    DisplayInformation(String.Format("Adding Table Name {0}, Use Bulk:",tableName));
                    if (taskType == ReplicationTaskType.Replicate)
                    {
                        replicateList.Add(para);
                        
                    }
                    else
                    {
                        refreshList.Add(para);
                    }
                }
            }
            

            _threadPool = new SmartThreadPool();
            _taskWorkGroup = _threadPool.CreateWorkItemsGroup(maxThreads);

            List<IWorkItemResult> waitFor = CreateTaskList(replicateList, refreshList);
            SmartThreadPool.WaitAll(waitFor.ToArray());

            // rerun failed tasks max #retries Times times
            int rerun = 0;
            while (rerun < Retries && _errorCount > 0)
            {
                waitFor = CreateTaskList(_FailedReplicateList, _FailedRefreshList);
                _FailedReplicateList.Clear();
                _FailedRefreshList.Clear();
                _errorCount = 0;
                _ErrorMessage = new StringBuilder();
                SmartThreadPool.WaitAll(waitFor.ToArray());
                rerun++;
            }

            string message;
            if (_errorCount== 0)
            {
                message = string.Format("Complete the task with no errors - {0:f} min",
                    (DateTime.Now - start).TotalMilliseconds / 60000);
                DisplayInformation(message);
                Debug.WriteLine(message);
                return true;
            }
            else
            {
                message = string.Format("Complete the task with {0} errors - {1:f} min", _errorCount.ToString(),
                    (DateTime.Now - start).TotalMilliseconds / 60000);
                DisplayError(message);
                Debug.WriteLine(message);
                return false;
            }

        }

        private static Dictionary<string, bool> GetExceptionList(string sqlDestConnectionString, ExceptionParameter exceptParam)
        {
            Dictionary<string, bool> list = new Dictionary<string, bool>();
            using (SqlConnection conn = new SqlConnection(sqlDestConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = String.Format("Select {2} from {0} where {1}=1", exceptParam.ExceptionTableName, exceptParam.ExceptionColumnName,exceptParam.ObjectColumnName);
                    DisplayInformation("Exceptions:" + cmd.CommandText);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            if (!list.ContainsKey(dr[0].ToString().ToLower()))
                            {
                                list.Add(dr[0].ToString().ToLower(), false);
                            }
                        }
                    }
                }
            }
            return list;
        }

        private static Dictionary<string, bool> GetForceBulkList(string sqlDestConnectionString, ExceptionParameter exceptParam)
        {
            Dictionary<string, bool> list = new Dictionary<string, bool>();
            using (SqlConnection conn = new SqlConnection(sqlDestConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = String.Format("Select {2} from {0} where {1}=1", exceptParam.ExceptionTableName, exceptParam.ForceBulkColumnName,exceptParam.ObjectColumnName);
                    DisplayInformation("ForceBulk:" + cmd.CommandText);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            if (!list.ContainsKey(dr[0].ToString()))
                            {
                                list.Add(dr[0].ToString(), false);
                            }
                        }
                    }
                }
            }
            return list;
        }

        private static Dictionary<string, bool> GetForceStandardList(string sqlDestConnectionString, ExceptionParameter exceptParam)
        {
            Dictionary<string, bool> list = new Dictionary<string, bool>();
            using (SqlConnection conn = new SqlConnection(sqlDestConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = String.Format("Select {2} from {0} where {1}=1", exceptParam.ExceptionTableName, exceptParam.ForceStandardColumnName, exceptParam.ObjectColumnName);
                    DisplayInformation("ForceStandard:" + cmd.CommandText);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            list.Add(dr[0].ToString(), false);
                        }
                    }
                }
            }
            return list;
        }
        private static List<IWorkItemResult> CreateTaskList(List<ReplicationParameter> replicateList, List<ReplicationParameter> refreshList)
        {
            
            List<IWorkItemResult> waitFor = new List<IWorkItemResult>();
            foreach(ReplicationParameter refresh in replicateList)
            {
                Action<ReplicationParameter> nextRefresh = ReplicateTable;
                IWorkItemResult result = _taskWorkGroup.QueueWorkItem(nextRefresh, refresh);
                waitFor.Add(result);
            }

            foreach (ReplicationParameter refresh in refreshList)
            {
                Action<ReplicationParameter> nextRefresh = RefreshTable;
                IWorkItemResult result = _taskWorkGroup.QueueWorkItem(nextRefresh, refresh);
                waitFor.Add(result);
            }

            return waitFor;
        }

        /// <summary>
        /// Replicate table
        /// </summary>
        /// <param name="state"></param>
        static void ReplicateTable(Object state)
        {
            DateTime start = DateTime.Now;
            ReplicationParameter rp = (ReplicationParameter)state;
            try
            {
                TableReplicator.Replicate(rp);
                if (_reportLevel == ReportingLevel.High)
                {
                    lock (objectLock)
                    {
                        string message = string.Format("Complete replicating table - {0}: {1:f} min",
                            rp.SFTableInfo.TableName, (DateTime.Now - start).TotalMilliseconds / 60000);
                        Debug.WriteLine(message);
                        DisplayInformation(message);
                    }
                }
            }
            catch (Exception ex)
            {
                lock (objectLock)
                {
                    string message = string.Format("Error when replicate table - {0}: {1}", rp.SFTableInfo.TableName, ex.Message);
                    Debug.WriteLine(message);
                    DisplayError(message);
                    _FailedReplicateList.Add(rp);
                }
            }
        }

        /// <summary>
        /// Refresh Table
        /// </summary>
        /// <param name="state"></param>
        static void RefreshTable(Object state)
        {
            DateTime start = DateTime.Now;
            ReplicationParameter rp = (ReplicationParameter)state;
            String type = "refreshing";
            
            try
            {

                if(!CheckTableStructure(rp))  //Lets see if we can fix the table structure before we do our real check
                {
                    //Failures can happen if:  There are a different number of Columns (need to add or remove a column or if the column definitions are different
                    TryFixTableStructure(rp);
                }

                if (CheckTableStructure(rp))
                {
                    
                    TableDeleter.Delete(rp);
                    string updateTableName = TableUpdater.Update(rp,true);
                    TableRefresher.Refresh(rp,updateTableName);
                    
                }
                else
                {
                    DisplayInformation("Starting Table Replication instead of Refresh for " + rp.SFTableInfo.TableName);
                    type = "replicating";
                    TableReplicator.Replicate(rp);
                }
                if (_reportLevel == ReportingLevel.High)
                {
                    lock (objectLock)
                    {
                        string message = string.Format("Complete {2} table - {0}: {1:f} min", rp.SFTableInfo.TableName,
                            (DateTime.Now - start).TotalMilliseconds / 60000, type);
                        Debug.WriteLine(message);
                        DisplayInformation(message);
                    }
                }

            }
            catch (Exception ex)
            {
                lock (objectLock)
                {
                    string message = string.Format("Error when {2} table - {0}: {1}", rp.SFTableInfo.TableName, ex.Message, type);
                    Debug.WriteLine(message);
                    _FailedRefreshList.Add(rp);
                    DisplayError(message);
                }
            }
        }

        public static void TryFixTableStructure(ReplicationParameter rp)
        {
            bool changesMade = false;
            using (SqlConnection Dest = new SqlConnection(rp.DestConnectionString))
            {

                Dest.Open();

                // check the structure of the table
                string[] restrictions = new string[4];
                restrictions[2] = rp.SFTableInfo.TableName;
                DataTable sqlTableSchema = Dest.GetSchema("Columns", restrictions);
                // save the sql table columns in the dictionary.
                Dictionary<string, DataRow> descColumns = new Dictionary<string, DataRow>();
                foreach (DataRow row in sqlTableSchema.Rows)
                {
                    if (!descColumns.ContainsKey(row["COLUMN_NAME"].ToString()))
                    {
                        descColumns.Add(row["COLUMN_NAME"].ToString(), row);
                    }
                }


                //Find Columns to Remove from Sql Server
                List<string> columnsToRemove = new List<string>();
                foreach (string sqlServerColumn in descColumns.Keys)
                {
                    if (!rp.SFTableInfo.ColumnNames.Contains(sqlServerColumn))
                    {
                        changesMade = true;
                        columnsToRemove.Add(sqlServerColumn);
                    }
                }

                //Find Columns to Add to Sql
                string baseAlterAdd = "ALTER table [{0}] add {1} ";
                List<string> failedAdds = new List<string>();
                foreach (string SalesforceColumn in rp.SFTableInfo.ColumnNames)
                {
                    if (!descColumns.ContainsKey(SalesforceColumn))
                    {
                        changesMade = true;
                        SqlCommand alterTableCommand = Dest.CreateCommand();
                        alterTableCommand.CommandText = String.Format(baseAlterAdd, rp.SFTableInfo.TableName, rp.SFTableInfo[SalesforceColumn].GetSqlServerCreateTableString());
                        try
                        {
                            alterTableCommand.ExecuteNonQuery();
                        }
                        catch 
                        {
                            failedAdds.Add(SalesforceColumn);
                        }

                    }
                }



                //Find Columns that need to be modified


                string baseAlterModify = "ALTER TABLE [{0}] ALTER COLUMN {1}";
                List<string> failedModifies = new List<string>();
                foreach (string salesforceColumnName in rp.SFTableInfo.ColumnNames)
                {
                    if (descColumns.ContainsKey(salesforceColumnName))
                    {
                        string type = descColumns[salesforceColumnName]["DATA_TYPE"].ToString();
                        int maxLength, scale, precision;
                        int.TryParse(descColumns[salesforceColumnName]["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength);
                        int.TryParse(descColumns[salesforceColumnName]["NUMERIC_SCALE"].ToString(), out scale);
                        int.TryParse(descColumns[salesforceColumnName]["NUMERIC_PRECISION"].ToString(), out precision);

                        if (!rp.SFTableInfo[salesforceColumnName].IsSameType(type, maxLength, scale, precision))
                        {
                            changesMade = true;
                            SqlCommand alterTableCommand = Dest.CreateCommand();
                            alterTableCommand.CommandText = String.Format(baseAlterModify, rp.SFTableInfo.TableName, rp.SFTableInfo[salesforceColumnName].GetSqlServerCreateTableString());
                            try
                            {
                                alterTableCommand.ExecuteNonQuery();
                            }
                            catch
                            {
                                failedModifies.Add(alterTableCommand.CommandText);
                            }
                        }
                    }
                }

                if (changesMade)
                {
                    bool sendEmail = false;
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(String.Format("Changes were made to the salesforce schema: Table {0}", rp.SFTableInfo.TableName));
                    sb.AppendLine();
                    if (columnsToRemove.Count > 0)
                    {
                        sb.AppendLine("Columns that may need to be removed by hand");
                        foreach (string column in columnsToRemove)
                        {
                            sb.AppendLine(column);
                        }
                        sendEmail = true;
                    }
                    if (failedAdds.Count > 0)
                    {
                        sb.AppendLine("Columns that failed to be added include");
                        foreach (string column in failedAdds)
                        {
                            sb.AppendLine(column);
                        }
                        sendEmail = true;
                    }
                    if (failedModifies.Count > 0)
                    {
                        sb.AppendLine("Columns that failed to be modified include");
                        foreach (string column in failedModifies)
                        {
                            sb.AppendLine(column);
                        }
                        sendEmail = true;
                    }

                    if (rp.EmailParameters.SendDmlEmail)
                    {
                        if (sendEmail)
                        {
                            //MailMessage mm = new MailMessage("SFSync@shutterstock.com", "business-intelligence@shutterstock.com", String.Format("Salesforce Table changes for {0}", rp.SFTableInfo.TableName), sb.ToString());
                            MailMessage mm = new MailMessage(rp.EmailParameters.FromEmail, rp.EmailParameters.ToEmail, String.Format("Salesforce Table changes for {0}", rp.SFTableInfo.TableName), sb.ToString());
                            SmtpClient client = new SmtpClient(rp.EmailParameters.SmtpAddress);
                            client.Send(mm);
                        }
                    }
                }

            } //End Using

            
        }

        /// <summary>
        /// Display the information
        /// </summary>
        /// <param name="message"></param>
        public static void DisplayInformation(string message)
        {
            if (_componentEvents != null)
            {
                bool b = false;
                _componentEvents.FireInformation(0, string.Empty, message, string.Empty, 0, ref b);
            }
            _Message.AppendLine(message);
            Debug.WriteLine(message);
        }

        /// <summary>
        /// Display the error
        /// </summary>
        /// <param name="message"></param>
        public static void DisplayError(string message)
        {
            if (_componentEvents != null)
            {
                _componentEvents.FireError(0, string.Empty, message, string.Empty, 0);
            }
            _Message.AppendLine(message);
            Debug.WriteLine(message);
            _ErrorMessage.AppendLine(message);
            _errorCount++;
            //Logger.LogEvent("SFDC", message, EventLogEntryType.Error);
        }

        /// <summary>
        /// Check the table structure, return false if there is any change.
        /// </summary>
        /// <param name="rp"></param>
        /// <returns>Whether the table structure has been changed.
        /// Return true if it is not changed, else return false.</returns>
        public static bool CheckTableStructure(ReplicationParameter rp)
        {

            using (SqlConnection Dest = new SqlConnection(rp.DestConnectionString))
            {

                Dest.Open();

                // check the structure of the table
                string[] restrictions = new string[4];
                restrictions[2] = rp.SFTableInfo.TableName;
                DataTable sqlTableSchema = Dest.GetSchema("Columns", restrictions);
                // save the sql table columns in the dictionary.
                Dictionary<string, DataRow> descColumns = new Dictionary<string, DataRow>();
                foreach (DataRow row in sqlTableSchema.Rows)
                {
                    if (!descColumns.ContainsKey(row["COLUMN_NAME"].ToString()))
                    {
                        descColumns.Add(row["COLUMN_NAME"].ToString(), row);
                    }
                }

                // get the table schema and check the structure. 
                if (rp.SFTableInfo.ColumnNames.Count != descColumns.Count)
                {
                    // if the two tables have different number of columns return false
                    return false;
                }
                else
                {
                    foreach (string s in rp.SFTableInfo.ColumnNames)
                    {
                        if (!descColumns.ContainsKey(s))
                        {
                            // if there the column is not in the sql database table return false
                            return false;
                        }
                        else
                        {
                            string type = descColumns[s]["DATA_TYPE"].ToString();
                            int maxLength, scale, precision;
                            int.TryParse(descColumns[s]["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength);
                            int.TryParse(descColumns[s]["NUMERIC_SCALE"].ToString(), out scale);
                            int.TryParse(descColumns[s]["NUMERIC_PRECISION"].ToString(), out precision);
                            if (!rp.SFTableInfo.ContainsColumn(s, type, maxLength, scale, precision))
                                return false;
                        }
                    }
                }
                return true;
            }
            
        }

        
    }
}
